package com.library.borrow.dao;

import com.library.borrow.model.BorrowRecord;
import com.library.util.JdbcUtil;
import com.library.util.PageModel;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class BorrowRecordDaoImpl implements BorrowRecordDao {
    private SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    @Override
    public int addBorrowRecord(BorrowRecord borrowRecord) {
        String sql = "INSERT INTO borrow_records (book_id, reader_name, reader_phone, borrow_date, return_date, status, createtime, updatetime) VALUES (?, ?, ?, ?, ?, ?, NOW(), NOW())";
        return JdbcUtil.jdbcByUpdate(sql, 
                borrowRecord.getBookId(), 
                borrowRecord.getReaderName(), 
                borrowRecord.getReaderPhone(), 
                borrowRecord.getBorrowDate() != null ? sdf.format(borrowRecord.getBorrowDate()) : null, 
                borrowRecord.getReturnDate() != null ? sdf.format(borrowRecord.getReturnDate()) : null, 
                borrowRecord.getStatus());
    }

    @Override
    public int updateBorrowRecord(BorrowRecord borrowRecord) {
        String sql = "UPDATE borrow_records SET book_id = ?, reader_name = ?, reader_phone = ?, borrow_date = ?, return_date = ?, actual_return_date = ?, status = ?, updatetime = NOW() WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, 
                borrowRecord.getBookId(), 
                borrowRecord.getReaderName(), 
                borrowRecord.getReaderPhone(), 
                borrowRecord.getBorrowDate() != null ? sdf.format(borrowRecord.getBorrowDate()) : null, 
                borrowRecord.getReturnDate() != null ? sdf.format(borrowRecord.getReturnDate()) : null, 
                borrowRecord.getActualReturnDate() != null ? sdf.format(borrowRecord.getActualReturnDate()) : null, 
                borrowRecord.getStatus(), 
                borrowRecord.getId());
    }

    @Override
    public int deleteBorrowRecordById(Integer id) {
        String sql = "DELETE FROM borrow_records WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, id);
    }

    @Override
    public BorrowRecord getBorrowRecordById(Integer id) {
        String sql = "SELECT * FROM borrow_records WHERE id = ?";
        List<Map<String, Object>> result = JdbcUtil.jdbcBySelect(sql, id);
        if (result != null && !result.isEmpty()) {
            return mapToBorrowRecord(result.get(0));
        }
        return null;
    }

    @Override
    public List<Map<String, Object>> getAllBorrowRecords() {
        String sql = "SELECT b.*, bk.title as book_title FROM borrow_records b LEFT JOIN books bk ON b.book_id = bk.id ORDER BY b.id DESC";
        return JdbcUtil.jdbcBySelect(sql);
    }

    @Override
    public List<Map<String, Object>> getBorrowRecordsByBookId(Integer bookId) {
        String sql = "SELECT b.*, bk.title as book_title FROM borrow_records b LEFT JOIN books bk ON b.book_id = bk.id WHERE b.book_id = ? ORDER BY b.id DESC";
        return JdbcUtil.jdbcBySelect(sql, bookId);
    }

    @Override
    public List<Map<String, Object>> getBorrowRecordsByReaderName(String readerName) {
        String sql = "SELECT b.*, bk.title as book_title FROM borrow_records b LEFT JOIN books bk ON b.book_id = bk.id WHERE b.reader_name = ? ORDER BY b.id DESC";
        return JdbcUtil.jdbcBySelect(sql, readerName);
    }

    @Override
    public List<Map<String, Object>> getBorrowRecordsByStatus(String status) {
        String sql = "SELECT b.*, bk.title as book_title FROM borrow_records b LEFT JOIN books bk ON b.book_id = bk.id WHERE b.status = ? ORDER BY b.id DESC";
        return JdbcUtil.jdbcBySelect(sql, status);
    }

    @Override
    public int updateBorrowRecordStatus(Integer id, String status) {
        String sql = "UPDATE borrow_records SET status = ?, updatetime = NOW() WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, status, id);
    }

    @Override
    public int returnBook(Integer id, Date actualReturnDate) {
        String sql = "UPDATE borrow_records SET actual_return_date = ?, status = '已还', updatetime = NOW() WHERE id = ?";
        return JdbcUtil.jdbcByUpdate(sql, actualReturnDate != null ? sdf.format(actualReturnDate) : null, id);
    }
    
    @Override
    public List<Map<String, Object>> getBorrowRecordsByPage(PageModel pageModel) {
        // 获取查询条件
        String likeValue = pageModel.getLikeValue() == null ? "" : pageModel.getLikeValue();
        
        // 构建SQL查询语句
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT b.*, bk.title as book_title FROM borrow_records b ");
        sql.append("LEFT JOIN books bk ON b.book_id = bk.id ");
        sql.append("WHERE b.id LIKE ? OR b.reader_name LIKE ? OR bk.title LIKE ? OR b.status LIKE ? ");
        
        // 计算分页起始位置
        int startIndex = (pageModel.getPageNo() - 1) * pageModel.getPageSize();
        
        // 添加排序和分页子句
        sql.append("ORDER BY b.id DESC LIMIT ?, ?");
        
        // 执行查询
        List<Object> params = new ArrayList<>();
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add("%" + likeValue + "%");
        params.add(startIndex);
        params.add(pageModel.getPageSize());
        
        return JdbcUtil.jdbcBySelect(sql.toString(), params.toArray());
    }
    
    @Override
    public int getBorrowRecordsCount(PageModel pageModel) {
        // 获取查询条件
        String likeValue = pageModel.getLikeValue() == null ? "" : pageModel.getLikeValue();
        
        // 构建统计SQL查询语句
        StringBuilder countSql = new StringBuilder();
        countSql.append("SELECT COUNT(*) as total FROM borrow_records b ");
        countSql.append("LEFT JOIN books bk ON b.book_id = bk.id ");
        countSql.append("WHERE b.id LIKE ? OR b.reader_name LIKE ? OR bk.title LIKE ? OR b.status LIKE ?");
        
        // 执行统计查询
        List<Map<String, Object>> countResult = JdbcUtil.jdbcBySelect(
            countSql.toString(), 
            "%" + likeValue + "%", 
            "%" + likeValue + "%", 
            "%" + likeValue + "%", 
            "%" + likeValue + "%"
        );
        
        if (countResult != null && !countResult.isEmpty()) {
            return Integer.parseInt(countResult.get(0).get("total").toString());
        }
        
        return 0;
    }

    // 辅助方法：将Map转换为BorrowRecord对象
    private BorrowRecord mapToBorrowRecord(Map<String, Object> map) {
        BorrowRecord borrowRecord = new BorrowRecord();
        borrowRecord.setId((Integer) map.get("id"));
        borrowRecord.setBookId((Integer) map.get("book_id"));
        borrowRecord.setReaderName((String) map.get("reader_name"));
        borrowRecord.setReaderPhone((String) map.get("reader_phone"));
        
        // 处理borrow_date
        Object borrowDateObj = map.get("borrow_date");
        if (borrowDateObj != null) {
            if (borrowDateObj instanceof Date) {
                borrowRecord.setBorrowDate((Date) borrowDateObj);
            } else if (borrowDateObj instanceof java.sql.Date) {
                borrowRecord.setBorrowDate(new Date(((java.sql.Date) borrowDateObj).getTime()));
            } else if (borrowDateObj instanceof Timestamp) {
                borrowRecord.setBorrowDate(new Date(((Timestamp) borrowDateObj).getTime()));
            } else if (borrowDateObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) borrowDateObj;
                borrowRecord.setBorrowDate(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        // 处理return_date
        Object returnDateObj = map.get("return_date");
        if (returnDateObj != null) {
            if (returnDateObj instanceof Date) {
                borrowRecord.setReturnDate((Date) returnDateObj);
            } else if (returnDateObj instanceof java.sql.Date) {
                borrowRecord.setReturnDate(new Date(((java.sql.Date) returnDateObj).getTime()));
            } else if (returnDateObj instanceof Timestamp) {
                borrowRecord.setReturnDate(new Date(((Timestamp) returnDateObj).getTime()));
            } else if (returnDateObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) returnDateObj;
                borrowRecord.setReturnDate(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        // 处理actual_return_date
        Object actualReturnDateObj = map.get("actual_return_date");
        if (actualReturnDateObj != null) {
            if (actualReturnDateObj instanceof Date) {
                borrowRecord.setActualReturnDate((Date) actualReturnDateObj);
            } else if (actualReturnDateObj instanceof java.sql.Date) {
                borrowRecord.setActualReturnDate(new Date(((java.sql.Date) actualReturnDateObj).getTime()));
            } else if (actualReturnDateObj instanceof Timestamp) {
                borrowRecord.setActualReturnDate(new Date(((Timestamp) actualReturnDateObj).getTime()));
            } else if (actualReturnDateObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) actualReturnDateObj;
                borrowRecord.setActualReturnDate(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        borrowRecord.setStatus((String) map.get("status"));
        
        // 处理createtime
        Object createTimeObj = map.get("createtime");
        if (createTimeObj != null) {
            if (createTimeObj instanceof Date) {
                borrowRecord.setCreatetime((Date) createTimeObj);
            } else if (createTimeObj instanceof Timestamp) {
                borrowRecord.setCreatetime(new Date(((Timestamp) createTimeObj).getTime()));
            } else if (createTimeObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) createTimeObj;
                borrowRecord.setCreatetime(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        // 处理updatetime
        Object updateTimeObj = map.get("updatetime");
        if (updateTimeObj != null) {
            if (updateTimeObj instanceof Date) {
                borrowRecord.setUpdatetime((Date) updateTimeObj);
            } else if (updateTimeObj instanceof Timestamp) {
                borrowRecord.setUpdatetime(new Date(((Timestamp) updateTimeObj).getTime()));
            } else if (updateTimeObj instanceof LocalDateTime) {
                LocalDateTime localDateTime = (LocalDateTime) updateTimeObj;
                borrowRecord.setUpdatetime(Date.from(localDateTime.atZone(ZoneId.systemDefault()).toInstant()));
            }
        }
        
        return borrowRecord;
    }
} 